#include <stdio.h>
#include <string.h>
#include <stdbool.h>
#include <cjson/cJSON.h>
#include <wchar.h>
#include "z_logger.h"
#include "sqlite.h"
#include "sql_dml.h"
#include "z_memory.h"
#include "value.h"
#include "redis.h"

#define METHOD_SELECT "SELECT"
#define METHOD_INSERT "INSERT"
#define METHOD_UPDATE "UPDATE"
#define METHOD_DELETE "DELETE"
#define METHOD_OPTION "OPTION"
#define METHOD_ACTIVE "ACTIVE"
#define METHOD_REBOOT "REBOOT"
#define METHOD_INFO "INFO"
/*
 * data_t 传入参数结构体，暂时只有计数count
 */
typedef struct {
    int count;
} data_t;

data_t *data_new() {
    return z_calloc(1, sizeof(data_t));
}

void data_free(data_t *obj) {
    if (!obj) return;
    z_free(obj);
}

static _Bool is_reserve_keys(const char *key) {
    static const char *reserve_keys[] = {PARAM_METHOD, PARAM_TABLE, PARAM_ID, PARAM_KEY};

    for (int j = 0; j < sizeof(reserve_keys) / sizeof(reserve_keys[0]); ++j) {
        if (0 == strcasecmp(reserve_keys[j], key)) {
            return true;
        }
    }

    return false;
}

static _Bool is_tm_reserve_keys(const char *key) {
    static const char *reserve_keys[] = {PARAM_METHOD, PARAM_TABLE, PARAM_ID, PARAM_KEY, PARAM_TAG, PARAM_SEGMENT};

    for (int j = 0; j < sizeof(reserve_keys) / sizeof(reserve_keys[0]); ++j) {
        if (0 == strcasecmp(reserve_keys[j], key)) {
            return true;
        }
    }

    return false;
}

char *param_to_sql_select(const query_param_t *param, char *sql, size_t size) {
    char *end = sql + size;

    const char *table = param_get_table(param);
    const char *key = param_get_key(param);

    sql += snprintf(sql, end - sql, "SELECT * FROM %s WHERE 1=1", table);
    if (strlen(key) != 0) {
        sql += snprintf(sql, end - sql, " AND %s = '%s'", !strcmp(table, "thing_model") ? "product" : "id", key);
    }
    for (int i = 0; i < param->size; ++i) {
        if (is_reserve_keys(param->key[i])) continue;
        const query_value_t *value = &param->val[i];
        sql += snprintf(sql, end - sql, " AND %s like '%%%s%%'", param->key[i], value->str_val);
    }
    return sql;
}

char *param_to_sql_option(const query_param_t *param, char *sql, size_t size) {
    char *end = sql + size;

    const char *table = param_get_table(param);
    const char *key = param_get_key(param);

    sql += snprintf(sql, end - sql, "SELECT %s, name FROM %s WHERE 1=1",
                    !strcmp(table, "thing_model") ? "product" : "id", table);
    if (strlen(key) != 0) {
        sql += snprintf(sql, end - sql, " AND %s = '%s'",
                        !strcmp(table, "thing_model") ? "product" : "id", key);
    }
    for (int i = 0; i < param->size; ++i) {
        if (is_reserve_keys(param->key[i])) continue;
        const query_value_t *value = &param->val[i];
        sql += snprintf(sql, end - sql, " AND %s = '%s'", param->key[i], value->str_val);
    }

    return sql;
}

char *param_to_sql_option_acquire(const query_param_t *param, char *sql, size_t size) {
    sql += snprintf(sql, size,
                    "SELECT d.id lru, m.segment * 0x1000 + m.tag + 1 tag, d.name || ':' || m.name name, m.metric,"
                    " s.name data_range, dt.name data_type, m.scale, m.adjust, m.writable, vt.name value_type, "
                    "m.time_tag has_timestamp FROM device d JOIN thing_model m ON d.type = m.product JOIN segment s "
                    "ON m.segment = s.id JOIN data_type dt ON m.data_type = dt.id JOIN value_type vt "
                    "ON m.value_type = vt.id WHERE (d.disable IS NULL OR d.disable = 0) order by d.id,tag");
    return sql;
}

char *param_to_sql_option_channel(const query_param_t *param, char *sql, size_t size) {
    sql += snprintf(sql, size,
                    "SELECT opt_id AS id, opt_name AS name, opt_type AS type, min_val, max_val, value "
                    "FROM channel_options WHERE channel='%s'",
                    param_get_key(param));
    return sql;
}

char *param_to_sql_option_type(const query_param_t *param, char *sql, size_t size) {
    sql += snprintf(sql, size,
                    "SELECT opt_val AS id, opt_name AS name "
                    "FROM opt_type_value WHERE opt_type='%s'",
                    param_get_key(param));
    return sql;
}

char *param_to_sql_insert(const query_param_t *param, char *sql, size_t size) {
    char *end = sql + size;

    const char *table = param_get_table(param);
    const char *key = param_get_key(param);

    if (!strcmp("thing_model", table)) {
        const char *segment = param_get_segment(param);
        const char *tag = param_get_tag(param);

        sql += snprintf(sql, end - sql, "INSERT INTO %s(idx,product,segment,tag", table);
        for (int i = 0; i < param->size; i++) {
            if (is_tm_reserve_keys(param->key[i])) continue;
            sql += snprintf(sql, end - sql, ", %s", param->key[i]);
        }
        sql += snprintf(sql, end - sql, ") VALUES (-1,'%s'", key);
        sql += snprintf(sql, end - sql, ", %s", segment);
        sql += snprintf(sql, end - sql, ", %s", tag);
        for (int i = 0; i < param->size; i++) {
            if (is_tm_reserve_keys(param->key[i])) continue;
            const query_value_t *value = &param->val[i];
            if (cJSON_String == value->type) {
                sql += snprintf(sql, end - sql, ",'%s'", value->str_val);
            } else if (cJSON_Number == value->type) {
                sql += snprintf(sql, end - sql, ",%f", value->dbl_val);
            } else {
                sql += snprintf(sql, end - sql, ",Null");
            }
        }
        sql += snprintf(sql, end - sql, ")");
    } else {
        sql += snprintf(sql, end - sql, "INSERT INTO %s(id", table);
        for (int i = 0; i < param->size; i++) {
            if (is_reserve_keys(param->key[i])) continue;
            sql += snprintf(sql, end - sql, ", %s", param->key[i]);
        }

        if (strlen(key) != 0) {
            sql += snprintf(sql, end - sql, ") VALUES ('%s'", key);
        } else {
            sql += snprintf(sql, end - sql, ") VALUES ((SELECT MAX(id) + 1 FROM %s)", table);
        }

        for (int i = 0; i < param->size; i++) {
            if (is_reserve_keys(param->key[i])) continue;
            const query_value_t *value = &param->val[i];
            if (cJSON_String == value->type) {
                sql += snprintf(sql, end - sql, ",'%s'", value->str_val);
            } else if (cJSON_Number == value->type) {
                sql += snprintf(sql, end - sql, ",%f", value->dbl_val);
            } else {
                sql += snprintf(sql, end - sql, ",Null");
            }
        }
        sql += snprintf(sql, end - sql, ")");
    }
    return sql;
}

char *param_to_sql_allactive(const query_param_t *param, char *sql, size_t size) {
    char *end = sql + size;
    const char *table = param_get_table(param);
    const char *key = param_get_key(param);
    sql += snprintf(sql, end - sql, "UPDATE %s SET disable = '%s'", table, key);
    sql += snprintf(sql, end - sql, " WHERE 1=1 ");
    for (int i = 0; i < param->size; i++) {
        if (is_reserve_keys(param->key[i])) continue;
        const query_value_t *value = &param->val[i];
        if (cJSON_String == value->type) {
            sql += snprintf(sql, end - sql, "and %s = '%s'", param->key[i], value->str_val);
        } else if (cJSON_Number == value->type) {
            sql += snprintf(sql, end - sql, "and %s = %f", param->key[i], value->dbl_val);
        } else {
            sql += snprintf(sql, end - sql, "and %s = Null", param->key[i]);
        }
    }
    return sql;
}

char *param_to_sql_update(const query_param_t *param, char *sql, size_t size) {
    char *end = sql + size;

    const char *table = param_get_table(param);
    const char *key = param_get_key(param);

    if (!strcmp("thing_model", table)) {
        const char *segment = param_get_segment(param);
        const char *tag = param_get_tag(param);
        sql += snprintf(sql, end - sql, "UPDATE %s SET product = '%s'", table, key);
        for (int i = 0; i < param->size; i++) {
            if (is_tm_reserve_keys(param->key[i])) continue;
            const query_value_t *value = &param->val[i];
            if (cJSON_String == value->type) {
                sql += snprintf(sql, end - sql, ", %s = '%s'", param->key[i], value->str_val);
            } else if (cJSON_Number == value->type) {
                sql += snprintf(sql, end - sql, ", %s = %f", param->key[i], value->dbl_val);
            } else {
                sql += snprintf(sql, end - sql, ", %s = Null", param->key[i]);
            }
        }
        sql += snprintf(sql, end - sql, " WHERE product = '%s' and segment = '%s' and tag = '%s'", key, segment, tag);
    } else {
        sql += snprintf(sql, end - sql, "UPDATE %s SET id = '%s'", table, key);
        for (int i = 0; i < param->size; i++) {
            if (is_reserve_keys(param->key[i])) continue;
            const query_value_t *value = &param->val[i];
            if (cJSON_String == value->type) {
                sql += snprintf(sql, end - sql, ", %s = '%s'", param->key[i], value->str_val);
            } else if (cJSON_Number == value->type) {
                sql += snprintf(sql, end - sql, ", %s = %f", param->key[i], value->dbl_val);
            } else {
                sql += snprintf(sql, end - sql, ", %s = Null", param->key[i]);
            }
        }
        sql += snprintf(sql, end - sql, " WHERE id = '%s'", key);
    }
    return sql;
}

char *param_to_sql_update_option(const query_param_t *param, char *sql, size_t size) {
    const char *key = param_get_key(param);

    sql += snprintf(sql, size, "UPDATE channel_options SET value='%s' WHERE channel='%s' AND opt_id='%s'",
                    param_get_string(param, "value"), key, param_get_string(param, "opt_id"));
    return sql;
}

char *param_to_sql_delete(const query_param_t *param, char *sql, size_t size) {
    char *end = sql + size;

    const char *table = param_get_table(param);
    const char *key = param_get_key(param);

    if (!strcmp("thing_model", table)) {
        const char *segment = param_get_segment(param);
        const char *tag = param_get_tag(param);
        sql += snprintf(sql, end - sql, "DELETE FROM %s WHERE product = '%s' "
                                        " and segment = '%s' and tag = '%s'", table, key, segment, tag);
    } else
        sql += snprintf(sql, end - sql, "DELETE FROM %s WHERE id = '%s'", table, key);
    return sql;
}

void print_device_segment(device_t *device, segment_e seg_id, cJSON *json) {
    int rc, i;
    cJSON *item = cJSON_CreateObject();

    vector_t key_list = vector_initializer;
    vector_t val_list = vector_initializer;

    vector_t *tag_list = &device->_product->thing_model[seg_id];
    for (i = 0; i < vector_size(tag_list); ++i) {
        thing_model_t *tag = vector_get(tag_list, i);

        vector_add(&key_list, tag);
    }
    cJSON_AddNumberToObject(item, "lru", device->super.id);
    cJSON_AddStringToObject(item, "name", device->super.name);
    cJSON_AddNumberToObject(item, "seg", seg_id);
    rc = redis_get_values(device, &key_list, &val_list);
    if (rc < 0) {
        goto end;
    }
    cJSON *array = cJSON_CreateArray();
    for (i = 0; i < vector_size(&key_list); ++i) {
        cJSON *value_item = cJSON_CreateObject();

        thing_model_t *key = vector_get(&key_list, i);
        value_t *val = vector_get(&val_list, i);

        cJSON_AddNumberToObject(value_item, "tag", key->super.id);

        if (0 != val->timestamp) {
            struct tm _tm;
            localtime_r(&val->timestamp, &_tm);
            cJSON_AddNumberToObject(value_item, "year", 1900 + _tm.tm_year);
            cJSON_AddNumberToObject(value_item, "month", _tm.tm_mon);
            cJSON_AddNumberToObject(value_item, "day", _tm.tm_mday);
            cJSON_AddNumberToObject(value_item, "hour", _tm.tm_hour);
            cJSON_AddNumberToObject(value_item, "min", _tm.tm_min);
            cJSON_AddNumberToObject(value_item, "sec", _tm.tm_sec);
        }
        cJSON_AddNumberToObject(value_item, "hex", val->_val);
        cJSON_AddNumberToObject(value_item, "val", val->value);
        cJSON_AddStringToObject(value_item, "key", key->super.name);

        char tag[MAX_INPUT];
        sprintf(tag, "%d", key->super.id);
        cJSON_AddItemToArray(array, value_item);
    }
    cJSON_AddItemToObject(item, "thing_model", array);
    cJSON_AddItemToArray(json, item);
    end:
    vector_clear(&key_list);
    vector_foreach(&val_list, (vector_foreach_callback) value_free);
    vector_clear(&val_list);
}

void print_device(device_t *device, cJSON *json) {
    int i;

    for (i = 0; i < sizeof(device->_product->thing_model) / sizeof(device->_product->thing_model[0]); ++i) {

        vector_t *tag_list = &device->_product->thing_model[i];
        if (vector_size(tag_list)) {
            print_device_segment(device, i, json);
        }
    }
}

cJSON *acquire_info_func(seqiot_t *seqiot) {
    cJSON *json = cJSON_CreateArray();
    for (int i = 0; i < vector_size(&seqiot->channels); ++i) {
        channel_t *channel = vector_get(&seqiot->channels, i);

        for (int j = 0; j < vector_size(&channel->devices); ++j) {
            device_t *device = vector_get(&channel->devices, j);
            if (channel->super.id == device->channel)
                print_device(device, json);
        }
    }
    return json;
}

int param_to_sql_get(const query_param_t *param, char *sql, size_t size) {
    if (param_method_is(param, METHOD_OPTION)) {
        const char *table = param_get_table(param);
        if (0 == strcasecmp("channel", table)) {
            param_to_sql_option_channel(param, sql, size);
        } else if (0 == strcasecmp("opt_type_value", table)) {
            param_to_sql_option_type(param, sql, size);
        } else if (0 == strcasecmp("acquire", table)) {
            param_to_sql_option_acquire(param, sql, size);
        } else {
            param_to_sql_option(param, sql, size);
        }
    } else {
        param_to_sql_select(param, sql, size);
    }
}

int param_to_sql_post(const query_param_t *param, char *sql, size_t size) {
    if (param_method_is(param, METHOD_INSERT)) {
        param_to_sql_insert(param, sql, size);
    } else if (param_method_is(param, METHOD_UPDATE)) {
        param_to_sql_update(param, sql, size);
    } else if (param_method_is(param, METHOD_DELETE)) {
        param_to_sql_delete(param, sql, size);
    } else if (param_method_is(param, METHOD_OPTION)) {
        param_to_sql_update_option(param, sql, size);
    } else if (param_method_is(param, METHOD_ACTIVE)) {
        param_to_sql_allactive(param, sql, size);
    } else if (param_method_is(param, METHOD_REBOOT)) {
        system("rm /mnt/conf/etc/thingmodel.db && cp /mnt/conf/etc/*.db /mnt/conf/etc/thingmodel.db && reboot");
    }
}

static int _sql_callback(void *data, int n_column, char **values, char **names) {
    cJSON *item = cJSON_CreateObject();
    for (int i = 0; i < n_column; i++) {
        cJSON_AddStringToObject(item, names[i], values[i]);
    }
    cJSON_AddItemToArray(data, item);

    return SQLITE_OK;
}

static int _sql_csv_callback(void *data, int n_column, char **values, char **names) {
    int i = 0;
    FILE *fp = stdout;
    data_t *send_data = data;
    if (0 == send_data->count) {
        for (int j = 0; j < n_column; j++) {
            if (j == n_column - 1)
                fprintf(fp, "%s\n", names[j]);
            else
                fprintf(fp, "%s,", names[j]);
        }
        send_data->count++;
    }

    for (; i < n_column - 1; i++) {
        if (NULL == values[i]) {
            fprintf(fp, ",");
        } else {
            fprintf(fp, "%s,", values[i]);
        }
    }
    if (NULL == values[i]) {
        fprintf(fp, "\n");
    } else
        fprintf(fp, "%s\n", values[i]);
    return SQLITE_OK;
}

void *sql_execute_csv(sqlite3 *db, const char *sql) {
    data_t *data = data_new();
    data->count = 0;
    int rc = sqlite3_exec(db, sql, _sql_csv_callback, data, NULL);
    log_debug("rc=%d, sql=[%s]", rc, sql);
    data_free(data);
    if (SQLITE_OK != rc) sqlite_check(db);
}

cJSON *sql_execute(sqlite3 *db, const char *sql) {
    cJSON *json = cJSON_CreateArray();

    int rc = sqlite3_exec(db, sql, _sql_callback, json, NULL);
    log_debug("rc=%d, sql=[%s]", rc, sql);
    if (SQLITE_OK != rc) sqlite_check(db);

    return json;
}

cJSON *sql_post_execute(sqlite3 *db, const char *sql) {
    cJSON *json = cJSON_CreateArray();
    cJSON *obj = cJSON_CreateObject();

    char error[MAX_INPUT];
    bzero(error, sizeof(error));
    int rc = sqlite3_exec(db, sql, _sql_callback, json, NULL);
    log_debug("rc=%d, sql=[%s]", rc, sql);
    if (SQLITE_OK != rc) {
        sqlite_post_check(error, db);
        cJSON_AddStringToObject(obj, "error_msg", error);
        cJSON_AddItemToArray(json, obj);
    }
    return json;
}